package com.pub.dbtools;

import org.jeecgframework.core.util.PropertiesUtil;
import org.jeecgframework.core.util.StringUtil;

import java.util.ArrayList;
import java.util.HashMap;

public class DataSimpDtoNew {

	private ArrayList arraydata;
	private int totrec;

	public DataSimpDtoNew(ArrayList newvec) {
		arraydata = newvec;
	}

	public DataSimpDtoNew(String sql, int npageno, int npagenum, String gsbm) {

		arraydata = DBQueryNew.makeQuery(sql, npageno, npagenum, gsbm);
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
		String totstr = (String) arraydata.get(arraydata.size() - 1);
		arraydata.remove(arraydata.size() - 1);
		totrec = Integer.parseInt(totstr);
	}

	public DataSimpDtoNew(String dbname, String sql, String gsbm) {
		if (dbname.equals("")) {
			arraydata = DBQueryNew.makeQuery(sql, gsbm);
		} else {
			if (dbname.toLowerCase().equals("operdb")) {
				PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
				 dbname = tmpvo.readProperty("jdbc.dbType");

			}
			arraydata = DBQueryNew.makeQueryDb(dbname, sql, gsbm);
		}
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
	}

	public DataSimpDtoNew(String sql, String gsbm) {
		arraydata = DBQueryNew.makeQuery(sql, gsbm);
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
	}

	public DataSimpDtoNew(String sql, int recnum, String gsbm) {
		arraydata = DBQueryNew.makeQuery(sql, recnum, gsbm);
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
	}

	public DataSimpDtoNew(String sql, int recnum, String sign, String gsbm) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");

		if (dbname.toLowerCase().startsWith("oracle")) {
			String newsql = "select * from (select my_table.*,rownum as my_rownum from(";
			newsql = newsql + sql
					+ ") my_table) where my_rownum>=0 and my_rownum<=" + recnum;

			arraydata = DBQueryNew.makeQuery(newsql, recnum, gsbm);
		}
		if (dbname.toLowerCase().startsWith("mysql")) {
			String newsql = sql + " limit 0," + recnum;
			arraydata = DBQueryNew.makeQuery(newsql, recnum, gsbm);
		}
		if (dbname.toLowerCase().startsWith("db2")) {
			String newsql = sql + " fetch first " + recnum + " rows only ";
			arraydata = DBQueryNew.makeQuery(newsql, recnum, gsbm);
		}
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}

	}

	public DataSimpDtoNew(String dbname, String sql, int recnum, String gsbm) {
		if (dbname.equals("")) {
			arraydata = DBQueryNew.makeQuery(sql, recnum, gsbm);
		} else {
			arraydata = DBQueryNew.makeQuery(dbname, sql, recnum, gsbm);
		}
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
	}

	public boolean isNull() {
		if (arraydata == null) {
			return true;
		} else {
			String[] tmptrr = (String[]) arraydata.get(0);
			if ((tmptrr[0]).equals("nullerror")) {
				return true;
			}
			return false;
		}
	}

	public DataSimpDtoNew() {
		arraydata = new ArrayList();
	}

	public final void clear() {
		arraydata = new ArrayList();
	}

	public final ArrayList getDataDto() {
		return arraydata;
	}

	public final int getTotField() {
		String[] tmpstr = (String[]) arraydata.get(0);
		return tmpstr.length;
	}

	public final int getTotRecord() {
		return arraydata.size() - 1;
	}

	public final String getFieldName(int n) {
		String[] tmpstr = (String[]) arraydata.get(0);
		return tmpstr[n];
	}

	public final String[] getFieldName() {
		String[] tmpstr = (String[]) arraydata.get(0);
		return tmpstr;
	}

	public final String getDataValue(String name, int recordno) {
		return DataDtoSupclass.getDataValue(arraydata, name.trim(), recordno);
	}

	public final String getDateValue(String name, int recordno) {
		String rqstr = DataDtoSupclass.getDataValue(arraydata, name.trim(),
				recordno).trim();
		if (rqstr.equals(""))
			return rqstr;

		return StringUtil.tostanddate(rqstr);
	}

	public final String getNumValue(String name, int recordno) {
		String nowvalue = DataDtoSupclass.getDataValue(arraydata, name.trim(),
				recordno);
		if (nowvalue.equals("")) {
			nowvalue = "0";
		}
		return nowvalue;
	}

	public final String getDataValue(int fieldno, int recordno) {
		return DataDtoSupclass.getDataValue(arraydata, fieldno, recordno);
	}

	public final HashMap getDataIndex(String name) {
		HashMap data = new HashMap();
		if (getTotRecord() > 0) {
			for (int i = 0; i < getTotRecord(); i++) {
				String value = getDataValue(name.trim(), i);
				if (data.containsKey(value)) {
					ArrayList indexArry = (ArrayList) data.get(value);
					indexArry.add("" + i);
					data.put(value, indexArry);
				} else {
					ArrayList indexArry = new ArrayList();
					indexArry.add("" + i);
					data.put(value, indexArry);
				}
			}
		}
		return data;
	}

	public final void setDataValue(String name, int recordno, String newvalue) {
		String[] tmparr = (String[]) arraydata.get(0);

		this.arraydata = DataDtoSupclass.setDataValue(arraydata, name.trim(),
				recordno, newvalue);
	}

	public final void setDataValue(int fieldno, int recordno, String newvalue) {
		this.arraydata = DataDtoSupclass.setDataValue(arraydata, fieldno,
				recordno, newvalue);
	}

	public DataSimpDtoNew(String selectsql, String fromSql, String searWhere,
			String searOrder, String groutpSql, String withSql, String kyename,
			String keyvalue, String gsbm) {
		String sql = makeStandSql(selectsql, fromSql, searWhere, searOrder,
				groutpSql, withSql, kyename, keyvalue);
		arraydata = DBQueryNew.makeQuery(sql, gsbm);
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
	}

	public final int getFullRecord() {
		return totrec;
	}

	public DataSimpDtoNew(String selectsql, String fromSql, String searWhere,
			String searOrder, String groutpSql, String withSql, int num,
			String gsbm) {
		String sql = makeStandSql(selectsql, fromSql, searWhere, searOrder,
				groutpSql, withSql, num);
		arraydata = DBQueryNew.makeQuery(sql, gsbm);
		if (arraydata == null) {
			arraydata = new ArrayList();
			String tmparr[] = { "nullerror" };
			arraydata.add(tmparr);
		}
	}

	private String makeStandSql(String selectsql, String fromSql,
			String searWhere, String searOrder, String groupSql,
			String withSql, int num) {
		String retsql = intiQuerySQL(withSql + selectsql, fromSql, searWhere,
				searOrder, groupSql, num);
		return retsql;
	}

	private String makeStandSql(String selectsql, String fromSql,
			String searWhere, String searOrder, String groupSql,
			String withSql, String keyname, String keyvalue) {
		String retsql = intiQuerySQL(withSql + selectsql, fromSql, searWhere,
				searOrder, keyname, keyvalue, groupSql);
		return retsql;
	}

	private String intiQuerySQL(String sql, String fromsql, String wheresql,
			String orderSql, String currKeyName, String currKeyValue,
			String groupsql) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");

		StringBuffer querySQL = new StringBuffer();
		sql = sql.toLowerCase();
		if (dbname.toLowerCase().startsWith("oracle")) {
			querySQL.append(
					"select * from (select my_table.*,rownum as my_rownum from(")
					.append(sql + " " + fromsql + " " + wheresql + " "
							+ groupsql + " " + orderSql)
					.append(") my_table ")
					.append(") where " + currKeyName + "='" + currKeyValue
							+ "'");
		}
		if (dbname.toLowerCase().startsWith("mysql")) {
			if (!wheresql.trim().equals("")) {
				wheresql += " and ";
			}
			wheresql += currKeyName + "='" + currKeyValue;
			querySQL.append(sql + " " + fromsql + " " + wheresql + " "
					+ groupsql + " " + orderSql);
		}
		if (dbname.toLowerCase().startsWith("db2")) {
			if (!wheresql.trim().equals("")) {
				wheresql += " and ";
			}
			wheresql += currKeyName + "='" + currKeyValue;
			querySQL.append(sql + " " + fromsql + " " + wheresql + " "
					+ groupsql + " " + orderSql);
		}
		return querySQL.toString();
	}

	private String intiQuerySQL(String sql, String fromsql, String wheresql,
			String orderSql, String groupsql, int startIndex) {
		PropertiesUtil tmpvo = new PropertiesUtil("dbconfig.properties");
		String dbname = tmpvo.readProperty("jdbc.dbType");


		StringBuffer querySQL = new StringBuffer();

		int totrow = 1 + startIndex;
		if (dbname.toLowerCase().startsWith("oracle")) {
			querySQL.append(
					"select * from (select my_table.*,rownum as my_rownum from(")
					.append(sql + " " + fromsql + " " + wheresql + " "
							+ groupsql + " " + orderSql).append(") my_table ")
					.append(") where my_rownum>=")
					.append(startIndex + " and my_rownum<" + totrow);
		}
		if (dbname.toLowerCase().startsWith("mysql")) {
			querySQL.append(
					sql + " " + fromsql + " " + wheresql + " " + groupsql + " "
							+ orderSql).append(
					"limit " + startIndex + "," + totrow);
		}
		if (dbname.toLowerCase().startsWith("db2")) {
			sql = sql + ",rownumber() over(" + orderSql + ") as rowid ";
			sql += fromsql + " " + wheresql + " " + groupsql;

			sql = "select * from (" + sql + ") as a where a.rowid > "
					+ startIndex + " AND a.rowid <=" + totrow;
			return sql;
		}

		return querySQL.toString();
	}
}
